using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace DocLibApp
{
    public partial class FormDatabase : FormBase
    {
        public FormDatabase()
        {
            InitializeComponent();
            LoadTableList();
            RefreshSaveQueryList();

            txtSQL.Select();
        }

        private void LoadTableList()
        {
            wait_start("Loading a list of table in database...");

            //System.Collections.ArrayList alist = FormMain.DocLib_AccessDB.GetListofTables();
            System.Collections.ArrayList alist = FormMain.DocLib_SQLite.GetListofTables();

            foreach (Object obj in alist)
            {
                cboTableList.Items.Add(obj.ToString());
            }

            wait_end();
        }

        private void btnRunSQLCommand_Click(object sender, EventArgs e)
        {
            string sql = txtSQL.Text.Trim();
            if (sql != "")
            {
                try
                {
                    if (sql.ToUpper().StartsWith("SELECT"))
                    {
                        DataTable dt = FormMain.DocLib_SQLite.GetDataTable(sql, "temp");
                        if (dt.Rows.Count > 0)
                        {
                            dgsql.DataSource = dt;

                            BindingSource myBS = new BindingSource();
                            myBS.DataSource = dt;

                            MybindingNavigator.BindingSource = myBS;
                        }
                    }
                    else
                    {
                        ShowMessage(FormMain.DocLib_SQLite.ExecuteSQL(sql));
                    }
                }
                catch (System.Exception ex)
                {
                    HandleEx(ex);
                }
            }
        }

        private void btnReset_Click(object sender, EventArgs e)
        {
            txtSQL.Text = "";
            dgsql.DataSource = null;
        }

        private void cboTableList_SelectionChangeCommitted(object sender, EventArgs e)
        {
            if (cboTableList.SelectedItem.ToString() != "")
            {
                string tablename = cboTableList.SelectedItem.ToString().Trim();
                DataTable dt = FormMain.DocLib_SQLite.GetDataTable("select * from " + tablename, "temp");
                dgsql.DataSource = dt;
            }
        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            string tablename = cboTableList.Text.Trim();

            string sFilter = "XML Files (*.xml)|*.xml";
            string filename = SelectFile(0, "Export datatable to file", sFilter, "test.xml");
            if (!string.IsNullOrEmpty(filename))
            {
                wait_start();
                FormMain.DocLib_SQLite.ExportDataTableToXML(tablename, filename);
                wait_end();
            }
        }

        private void saveSQLQueryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            string sql = txtSQL.Text.Trim();
            int iLength = 25;

            if (!string.IsNullOrEmpty(sql))
            {
                if (sql.Length < 25)
                    iLength = sql.Length;

                doc_system.AddToList(def.SaveQueryList_Group, sql.Substring(0, iLength).Trim(), sql, def.SaveQueryList_Limit);
                RefreshSaveQueryList();
            }
        }

        private void RefreshSaveQueryList()
        {
            System.Collections.Hashtable ht = doc_system.GetAList(def.SaveQueryList_Group);

            if (ht.Count > 0)
            {
                //clear old list if exists
                if (recentlySavedSQLQueriesToolStripMenuItem.DropDownItems.Count > 0)
                    recentlySavedSQLQueriesToolStripMenuItem.DropDownItems.Clear();

                foreach (string key in ht.Keys)
                {
                    string desc = ht[key].ToString();

                    System.Windows.Forms.ToolStripMenuItem aItem = new ToolStripMenuItem();

                    aItem.Tag = key;
                    aItem.Text = desc;
                    aItem.Click += new EventHandler(SaveQuerySelect_Click);

                    recentlySavedSQLQueriesToolStripMenuItem.DropDownItems.Add(aItem);
                }
            }
        }

        private void SaveQuerySelect_Click(object sender, EventArgs e)
        {
            if (sender is ToolStripItem)
            {
                ToolStripItem aitem = sender as ToolStripItem;
                if (!string.IsNullOrEmpty(aitem.Text))
                {
                    txtSQL.Text = aitem.Text;
                }
            }
        }
    }
}